package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.wms.WMSAreaStock;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class WMSAreaStockDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(WMSAreaStockDAO.class);

    private static WMSAreaStockDAO Instance = null;

    private WMSAreaStockDAO() {
        super();
    }

    public static WMSAreaStockDAO getInstance() {
        if (Instance == null)
            Instance = new WMSAreaStockDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, WMSAreaStock wWMSAreaStock, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wWMSAreaStock == null)
                return 0;

            String wSQL = "";
            if (wWMSAreaStock.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.wms_areastock(AreaID,MaterialID,MaterialNo,MaterialName," +
                                "FQTYTotal,FQTY,BatchNumber,SuplierID,SuplierName,SuplierCode,SerialNo,CreateID," +
                                "CreateTime,EditID,EditTime) VALUES(:AreaID,:MaterialID,:MaterialNo,:MaterialName," +
                                ":FQTYTotal,:FQTY,:BatchNumber,:SuplierID,:SuplierName,:SuplierCode,:SerialNo," +
                                ":CreateID,:CreateTime,:EditID,:EditTime);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.wms_areastock SET AreaID = :AreaID,MaterialID = :MaterialID," +
                                "MaterialNo = :MaterialNo,MaterialName = :MaterialName,FQTYTotal = :FQTYTotal,FQTY = " +
                                ":FQTY,BatchNumber = :BatchNumber,SuplierID = :SuplierID,SuplierName = :SuplierName," +
                                "SuplierCode = :SuplierCode,SerialNo = :SerialNo,CreateID = :CreateID,CreateTime = " +
                                ":CreateTime,EditID = :EditID,EditTime = :EditTime WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wWMSAreaStock.ID);
            wParamMap.put("AreaID", wWMSAreaStock.AreaID);
            wParamMap.put("MaterialID", wWMSAreaStock.MaterialID);
            wParamMap.put("MaterialNo", wWMSAreaStock.MaterialNo);
            wParamMap.put("MaterialName", wWMSAreaStock.MaterialName);
            wParamMap.put("FQTYTotal", wWMSAreaStock.FQTYTotal);
            wParamMap.put("FQTY", wWMSAreaStock.FQTY);
            wParamMap.put("BatchNumber", wWMSAreaStock.BatchNumber);
            wParamMap.put("SuplierID", wWMSAreaStock.SuplierID);
            wParamMap.put("SuplierName", wWMSAreaStock.SuplierName);
            wParamMap.put("SuplierCode", wWMSAreaStock.SuplierCode);
            wParamMap.put("SerialNo", wWMSAreaStock.SerialNo);
            wParamMap.put("CreateID", wWMSAreaStock.CreateID);
            wParamMap.put("CreateTime", wWMSAreaStock.CreateTime);
            wParamMap.put("EditID", wWMSAreaStock.EditID);
            wParamMap.put("EditTime", wWMSAreaStock.EditTime);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wWMSAreaStock.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wWMSAreaStock.setID(wResult);
            } else {
                wResult = wWMSAreaStock.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<WMSAreaStock> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (WMSAreaStock wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = StringUtils.Format("delete from {1}.wms_areastock WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSAreaStock SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSAreaStock wResult = new WMSAreaStock();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSAreaStock> wList = SelectList(wLoginUser, wID, -1, "", -1, "", "", null, null, wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<WMSAreaStock> SelectList(BMSEmployee wLoginUser, int wID, int wAreaID, String wBatchNumber,
                                         int wSuplierID, String wSerialNo, String wMaterial, Calendar wStartTime,
                                         Calendar wEndTime,
                                         OutResult<Integer> wErrorCode) {
        List<WMSAreaStock> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (StringUtils.isEmpty(wMaterial))
                wMaterial = "";

            String wSQL = MessageFormat.format(
                    "SELECT t.* FROM {0}.wms_areastock t,t1.Name AreaName "
                            + " left join {0}.bms_department t1 on t.AreaID=t1.ID "
                            + "WHERE  1=1  and ( :wID <= 0 or :wID = t.ID ) and ( :wAreaID <= 0 " +
                            "or :wAreaID = t.AreaID ) and ( :wBatchNumber is null or :wBatchNumber = '''' or " +
                            ":wBatchNumber = t.BatchNumber ) and ( :wSuplierID <= 0 or :wSuplierID = t.SuplierID ) " +
                            "and ( " +
                            ":wSerialNo is null or :wSerialNo = '''' or :wSerialNo = t.SerialNo )and ( :wStartTime " +
                            "<=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  t.EditTime ) and ( " +
                            ":wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  t.CreateTime ) " +
                            "and (:Material=null or :Material='''' or (t.MaterialName like ''%{1}%'' or t.MaterialNo " +
                            "like" +
                            " ''%{1}%''));",
                    wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wAreaID", wAreaID);
            wParamMap.put("wBatchNumber", wBatchNumber);
            wParamMap.put("wSuplierID", wSuplierID);
            wParamMap.put("wSerialNo", wSerialNo);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("Material", wMaterial);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                WMSAreaStock wItem = new WMSAreaStock();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.AreaID = StringUtils.parseInt(wReader.get("AreaID"));
                wItem.AreaName = StringUtils.parseString(wReader.get("AreaName"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.FQTYTotal = StringUtils.parseDouble(wReader.get("FQTYTotal"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.SuplierID = StringUtils.parseInt(wReader.get("SuplierID"));
                wItem.SuplierName = StringUtils.parseString(wReader.get("SuplierName"));
                wItem.SuplierCode = StringUtils.parseString(wReader.get("SuplierCode"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.CreateID = StringUtils.parseInt(wReader.get("CreateID"));
                wItem.Creator = APSConstans.GetBMSEmployeeName(wItem.CreateID);
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.EditID = StringUtils.parseInt(wReader.get("EditID"));
                wItem.Editor = APSConstans.GetBMSEmployeeName(wItem.EditID);
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }
}
